#!/usr/bin/env python
# -*- coding:utf-8 -*-
# @Author:    御风
# @Mail:      zhong.yufeng@foxmail.com
# @Time:      2022/4/10 17:45


import pandas as pd
from sqlalchemy import Column, Integer, String, Date, DateTime
from sqlalchemy.exc import IntegrityError
from utils.db.module import Base
from utils.db.database import DataBase
from utils.helper import md5


class AdminItem(Base):
    """
    CREATE TABLE `sys_admin` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `account` varchar(32) NOT NULL,
        `password` varchar(32) NOT NULL,
        `valid` int(10) unsigned NOT NULL DEFAULT 0,
        `create_time` timestamp NULL DEFAULT current_timestamp(),
        `update_time` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
        PRIMARY KEY (`id`),
        UNIQUE KEY `account` (`account`)
    ) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;
    """

    __tablename__ = "sys_admin"

    id = Column(Integer, nullable=False, primary_key=True, autoincrement=True)
    account = Column(String(32), nullable=False, unique=True)
    password = Column(String(32), nullable=False)
    valid = Column(Integer, nullable=False, default=0)
    create_time = Column(DateTime, nullable=True)
    update_time = Column(DateTime, nullable=True)


class AdminDataBase(DataBase):

    def __init__(self):
        super(AdminDataBase, self).__init__()
        self.table = AdminItem.__tablename__

    def query(self, account: str, password: str = None):
        where = ""
        if account is not None and account != "":
            where = "WHERE" if where == "" else where
            where = f"{where} `account`=:account"
        if password is not None and password != "":
            where = "WHERE" if where == "" else f"{where} AND"
            where = f"{where} `password`=:password"

        sql = f"SELECT `id`, `account`, `password`, `valid` FROM `{self.table}` {where} LIMIT 0, 1;"
        df = pd.read_sql(self.text(sql), self.engine, params={
            "account":  account,
            "password": md5(f"#{password}#"),
        })
        result = None
        if len(df) > 0:
            result = {
                "id":       df.iloc[0]["id"].item(),
                "account":  df.iloc[0]["account"],
                "password": df.iloc[0]["password"],
                "valid":    df.iloc[0]["valid"].item() != 0,
            }
        return result

    def add(self, account: str, password: str, valid: bool = True):
        result = False
        valid = 1 if valid else 0
        try:

            # 方法 1:
            # sql = f"INSERT INTO `{self.table}` (`account`, `password`, `valid`) VALUE (:account, :password, :valid);"
            # cursor = self.engine.execute(self.text(sql), {
            #     "account":  account,
            #     "password": md5(f"#{password}#"),
            #     "valid":    valid,
            # })

            # 方法 2:
            values = [{
                "account":  account,
                "password": md5(f"#{password}#"),
                "valid":    valid,
            }]
            cursor = self.engine.execute(AdminItem.__table__.insert(), values)

            if cursor.rowcount > 0:
                result = True
            cursor.close()
        except IntegrityError as e:
            # 数据重复
            if "Duplicate entry" in str(e):
                return False
            raise e

        return result

    def update(self, adminId: int, account: str, password: str, newPass: str):
        result = None
        sql = f"UPDATE `{self.table}` SET `password`=:newPass WHERE `id`=:adminId AND `account`=:account AND `password`=:password LIMIT 1"
        cursor = self.engine.execute(self.text(sql), {
            "adminId":  adminId,
            "account":  account,
            "password": md5(f"#{password}#"),
            "newPass":  md5(f"#{newPass}#"),
        })
        if cursor.rowcount > 0 or password == newPass:
            result = {
                "id":      adminId,
                "account": account,
            }
        cursor.close()
        return result


if __name__ == "__main__":
    import json

    db = AdminDataBase()
    print(db.query("test"))

    # print(db.query("test", "test"))
    # print(db.add("test", "test", True))
    # print(db.update(1, "test", "test", "test"))
